﻿using Avalonia.Markup.Xaml.MarkupExtensions;
using LiveChartsCore.Themes;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using 畜牧业审核程序国产.Models.ToolsClass;

namespace 畜牧业审核程序国产.Models.SimpleFactory
{
    internal class Pig : Animal
    {
        string withYearAndSeasonTableNameNow = Global.GetWithPlatformYearSeason().Item1 + "A209";
        string withYearAndSeasonTableNamePre = Global.GetWithPlatformYearSeason().Item2 + "A209";
        string withYearAndSeasonTableNameYOY = Global.GetWithPlatformYearSeason().Item3 + "A209";
        public override string[] ArrIndicators => new string[] {"当季出栏数", "期末存栏合计01", "25公斤以下仔猪存栏数02",
        "待育肥猪存栏数03","50公斤以上待育肥猪存栏数04","种猪存栏数05","能繁殖母猪存栏数06","当季增加头数07","自繁头数08","购进头数09",
        "当季减少头数10","自宰肥猪头数11", "出售肥猪头数（不包括出售待育肥猪）12","出售肥猪金额（元）13",
        "出售肥猪重量（公斤）14","其它原因减少（包括死亡、疫病捕杀）15","出售25公斤以下仔猪头数16","出售25公斤以下仔猪金额（元）17",
        "出售25公斤以下仔猪重量（公斤）18"};

        public override DataTable GetDTAllIndicatorsOfSubordinateThisYearByCity(string[] tableNames,int 期别季)
        {
            MyDBOperator getSimpleAddOperator = MyDBOperator.DBOperatorInstance;
            getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(tableNames);
            string sqlToGetThisYear =
                 $"  WITH 存栏合计 AS(SELECT 市, 市代码, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}' " +
                        " GROUP BY 市,市代码), " +
                     " 出栏合计 AS(SELECT 市,市代码, SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         " FROM 一到本季度用表 " +
                         " GROUP BY 市,市代码 )" +
                      "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                     " ORDER BY  市代码; ";
            DataTable dt = getSimpleAddOperator.GetDataTable(sqlToGetThisYear);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 2; i < dt.Columns.Count; i++)
            {
                var sum = dt.AsEnumerable().Sum(row => row.Field<double>(i));
                sumRow[i] = sum;
            }
            dt.Rows.Add(sumRow);
            return dt;
          
        }
        public override DataTable GetDTAllIndicatorsOfSubordinateThisYearByTown(KeyValuePair<string,string> selectedCountry, string[] tableNames, int 期别季)
        {
            MyDBOperator getSimpleAddOperator = MyDBOperator.DBOperatorInstance;
            getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(tableNames);
            string sqlToGetThisYear =
                 $"  WITH 存栏合计 AS(SELECT 乡, 乡镇代码, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}' " +
                        " GROUP BY 乡,乡镇代码), " +
                     " 出栏合计 AS(SELECT 乡,乡镇代码, SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         " FROM 一到本季度用表 " +
                         " GROUP BY 乡,乡镇代码 )" +
                      "SELECT COALESCE(c.乡, o.乡) AS 乡, COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码, COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.乡 = o.乡 and c.乡镇代码 = o.乡镇代码" +
                     " ORDER BY  乡镇代码; ";
            DataTable dt = getSimpleAddOperator.GetDataTable(sqlToGetThisYear);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 2; i < dt.Columns.Count; i++)
            {
                var sum = dt.AsEnumerable().Sum(row => row.Field<double>(i));
                sumRow[i] = sum;
            }
            dt.Rows.Add(sumRow);
            return dt;

        }
        /// <summary>
        /// 获取两个DataTable,分别是按县的1到本季度的存栏合计数和出栏合计数
        /// </summary>
        /// <param name="cityKey"></param>
        /// <param name="tableNames"></param>
        /// <param name="期别季"></param>
        /// <returns></returns>
        public override DataTable GetDTAllIndicatorsOfSubordinateThisYearByCountry(KeyValuePair<string,string> selectedCity, string[] tableNames,int 期别季)
        {
            //可以有两种方式:1是把从季度开始到现在的所有的209表全部组合并复制到一个数据库中一个新的表(可以通过简单的  dataTable.Load(reader);也可以通过复杂的先查询出多个datatable ,再通过MergeMultipleTablesSimpleRowAddToNewTable
            string strAllIndicatorsJoin = GetNameAllIncatorsJoin();//把所有
            List<string> sqlsToGetTables = new List<string>();
            foreach (string tableName in tableNames)
            {
                sqlsToGetTables.Add($"select * from {tableName} where 市代码 = '{selectedCity.Key}' ");
            
            }
            MyDBOperator GetSimpleAddOperator = MyDBOperator.DBOperatorInstance;
            //DataTable dataTableSimpleAdd = GetSimpleAddOperator.MergeTablesFromDatabaseToDataTable(tableNames);这个没用了,但是很节省时间,要用的话需用linq进行查询



            //GetSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(sqlsToGetTables.ToArray());//复杂的方法
            GetSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(tableNames);//简单的方法




            string strSumAllIndicatorsJoin = GetSumStrAllIndicatorsJoin();
            ////string sqlToGetAllSumIndicatorsByCountry = $"select 县,县代码,{strSumAllIndicatorsJoin} from {withYearAndSeasonTableNameNow} where 市代码 = '{cityKey}' group by 县,县代码";
            //List<string> sqls = new List<string>();
            //foreach (var tableName in tableNames)
            //{
            //    sqls.Add($"select 县,县代码,{strSumAllIndicatorsJoin} from {tableName} where 市代码 = '{cityKey}' group by 县,县代码");
            //}


            //MyDBOperator myoperator = MyDBOperator.DBOperatorInstance;
            //bool 是否已经把一到本季度的所有表组合并写入新的表 = myoperator.MergeTablesFromDatabaseToAMergeTableInDB(sqls.ToArray());
            //if (是否已经把一到本季度的所有表组合并写入新的表 ==false)
            //{
            //    return null;
            //}

            //要先把存栏的查询出来

          string sqlToGet =$"  WITH 存栏合计 AS(SELECT 县, 县代码, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}' " +
                        " GROUP BY 县,县代码), " +
                     " 出栏合计 AS(SELECT 县,县代码, SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         " FROM 一到本季度用表 " +
                         " GROUP BY 县,县代码 )" +
                      "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                     " ORDER BY  县代码; ";

            DataTable dt = GetSimpleAddOperator.GetDataTable(sqlToGet);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 2; i < dt.Columns.Count; i++)//不用每个都求和
            {
                var sum = dt.AsEnumerable().Sum(row => row.Field<double>(i));
                sumRow[i] = sum;
            }
            dt.Rows.Add(sumRow);
            return dt;
            //string sqlToGet存栏 = $"select 县,县代码,{strSumAllIndicatorsJoin} from 一到本季度用表 where 市代码 = '{selectedCity.Key}' and 期别季 = '{期别季}' group by 县,县代码 order by 县代码";
            //DataTable dt存栏 = GetSimpleAddOperator.GetDataTable(sqlToGet存栏);
            //DataRow sumRowOf存栏 = dt存栏.NewRow();
            //sumRowOf存栏[0] = "规模户合计";
            //for (int i = 2; i < dt存栏.Columns.Count; i++)//不用每个都求和
            //{
            //    var sum = dt存栏.AsEnumerable().Sum(row => row.Field<double>(i));
            //    sumRowOf存栏[i] = sum;
            //}
            //dt存栏.Rows.Add(sumRowOf存栏);
            ////再求出栏
            //string sqlToGet出栏 = $"select 县,县代码,{strSumAllIndicatorsJoin} from 一到本季度用表 where 市代码 = '{selectedCity.Key}'  group by 县,县代码 order by 县代码";
            //DataTable dt出栏 = GetSimpleAddOperator.GetDataTable(sqlToGet出栏);
            //DataRow sumRowOf出栏 = dt出栏.NewRow();
            //for (int i = 2; i < dt存栏.Columns.Count; i++)
            //{
            //    var sum = dt出栏.AsEnumerable().Sum(r => r.Field<double>(i));
            //    sumRowOf出栏[i]=sum;
            //}
            //dt出栏.Rows.Add(sumRowOf出栏 );
            //DataTable[] dtall = new DataTable[] { dt存栏, dt出栏 };
            //return dtall;

            

        }
        /// <summary>
        /// 返回分地市的规模户汇总数据
        /// </summary>
        /// <param name="city"></param>
        /// <returns></returns>
        public override DataTable GetDTAllIndicatorsOfSubordinateThisSeasonByCity()
        {
            string strAllIndicatorsJoin = GetSumStrAllIndicatorsJoin();
            string sqlToGetAllSumIndicatorsByCity = $"select 市,市代码,{strAllIndicatorsJoin} from {withYearAndSeasonTableNameNow} group by 市,市代码";
            MyDBOperator myoperator = MyDBOperator.DBOperatorInstance;
            DataTable dt = myoperator.GetDataTable(sqlToGetAllSumIndicatorsByCity);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 2; i < dt.Columns.Count; i++)
            {
                var sum = dt.AsEnumerable().Sum(row => row.Field<double>(i));
                sumRow[i] = sum;
            }
            dt.Rows.Add(sumRow);
            return dt;
        }
/// <summary>
/// 返回本季度某个地市下属分县的规模户数据
/// </summary>
/// <param name="city"></param>
/// <param name="country"></param>
/// <returns></returns>
        public override DataTable GetDTAllIndicatorsOfSubordinateThisSeasonByCountry(KeyValuePair<string,string> selectedCity)
        {
            string strSumAllIndicatorsJoin = GetSumStrAllIndicatorsJoin();
            string sqlToGetAllSumIndicatorsByCountry= $"select 县,县代码,{strSumAllIndicatorsJoin} from {withYearAndSeasonTableNameNow} where 市代码 = '{selectedCity.Key}' group by 县,县代码";
            MyDBOperator myoperator = MyDBOperator.DBOperatorInstance;
            DataTable dt = myoperator.GetDataTable(sqlToGetAllSumIndicatorsByCountry);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 2; i < dt.Columns.Count; i++)
            {
                var sum = dt.AsEnumerable().Sum(row => row.Field<double>(i));
                sumRow[i] = sum;
            }
            dt.Rows.Add(sumRow);
            return dt;
        }
        public override DataTable GetDTAllIndicatorsOfSubordinateThisSeasonByTown(KeyValuePair<string, string> selectedCountry)
        {
            string strSumAllIndicatorsJoin = GetSumStrAllIndicatorsJoin();
            string sqlToGetAllSumIndicatorsByTown  = $"SELECT 乡,乡镇代码,{strSumAllIndicatorsJoin} from {withYearAndSeasonTableNameNow} where 县代码 = '{selectedCountry.Key}' group by 乡,乡镇代码 order by 乡镇代码";
            MyDBOperator myoperator = MyDBOperator.DBOperatorInstance;
            DataTable dt = myoperator.GetDataTable(sqlToGetAllSumIndicatorsByTown);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 2; i < dt.Columns.Count; i++)
            {
                var sum = dt.AsEnumerable().Sum(row => row.Field<double>(i));
                sumRow[i] = sum;
            }
            dt.Rows.Add(sumRow);
            return dt;
        }
        /// <summary>
        /// 返回某个市的全部养殖户数据
        /// </summary>
        /// <param name="city"></param>
        /// <returns></returns>
        public override DataTable GetDTAllIndicatorsOfAllOwnersLocalThisSeasonByCity()
        {
            string strAllindicatorsNamesJoin = this.GetNameAllIncatorsJoin();
            string sqlToGetDTByCity
                 = $"select 市, 县,乡,户主姓名,养殖户单位类型,{strAllindicatorsNamesJoin} from {withYearAndSeasonTableNameNow} ";
            MyDBOperator myDBOperator = MyDBOperator.DBOperatorInstance;
            DataTable dt = myDBOperator.GetDataTable(sqlToGetDTByCity);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 5; i < dt.Columns.Count; i++)
            {
                sumRow[i] = dt.AsEnumerable().Sum(row => row.Field<double>(i));

            }
            dt.Rows.Add(sumRow);
            return dt;
        }


/// <summary>
/// 返回某个县的全部规模户数据
/// </summary>
/// <param name="country"></param>
/// <returns></returns>
        public override DataTable GetDTAllIndicatorsOfAllOwnersLocalThisSeasonByCountry(KeyValuePair<string,string> selectedCity)
        {
            string strAllindicatorsNamesJoin = this.GetNameAllIncatorsJoin();
            string sqlToGetDTByCity
                 = $"select 市, 县,乡,户主姓名,养殖户单位类型,{strAllindicatorsNamesJoin} from {withYearAndSeasonTableNameNow} where 市代码 = '{selectedCity.Key}'";
            MyDBOperator myDBOperator = MyDBOperator.DBOperatorInstance;
            DataTable dt = myDBOperator.GetDataTable(sqlToGetDTByCity);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 5; i < dt.Columns.Count; i++)
            {
                sumRow[i] = dt.AsEnumerable().Sum(row => row.Field<double>(i));

            }
            dt.Rows.Add(sumRow);
            return dt;
        }
/// <summary>
/// 返回某个乡镇的全部规模户数据
/// </summary>
/// <param name="town"></param>
/// <returns></returns>
        public override DataTable GetDTAllIndicatorsOfAllOwnersLocalThisSeasonByTown(KeyValuePair<string,string> selectedCountry)
        {
            string strAllindicatorsNamesJoin = this.GetNameAllIncatorsJoin();
            string sqlToGetDTByCity
                 = $"select 市, 县,乡,户主姓名,养殖户单位类型, {strAllindicatorsNamesJoin} from {withYearAndSeasonTableNameNow} where 县代码 = '{selectedCountry.Key}'";
            MyDBOperator myDBOperator = MyDBOperator.DBOperatorInstance;
            DataTable dt = myDBOperator.GetDataTable(sqlToGetDTByCity);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 5; i < dt.Columns.Count; i++)
            {
                sumRow[i] = dt.AsEnumerable().Sum(row => row.Field<double>(i));

            }
            dt.Rows.Add(sumRow);
            return dt;
        }
        public override DataTable GetDTAllIndicatorsOfAllOwnersLocalThisSeasonOfASelectedTown(KeyValuePair<string, string> selectedTown)
        {
            string strAllindicatorsNamesJoin = this.GetNameAllIncatorsJoin();
            string sqlToGetDTByCity
                 = $"select 市, 县,乡,户主姓名,养殖户单位类型, {strAllindicatorsNamesJoin} from {withYearAndSeasonTableNameNow} where 乡镇代码 = '{selectedTown.Key}'";
            MyDBOperator myDBOperator = MyDBOperator.DBOperatorInstance;
            DataTable dt = myDBOperator.GetDataTable(sqlToGetDTByCity);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 5; i < dt.Columns.Count; i++)
            {
                sumRow[i] = dt.AsEnumerable().Sum(row => row.Field<double>(i));

            }
            dt.Rows.Add(sumRow);
            return dt;
        }
        public override DataTable GetDTSingleIndicatorOfSubordinateThisSeasonByCity(KeyValuePair<string, string> Indicator)
        {
            MyDBOperator adoForShowSingleMsg = MyDBOperator.DBOperatorInstance;
            //先复制三个表
            adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllCityList", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });

            DataTable dt1 = adoForShowSingleMsg.GetDataTable($"select 统计区划名称 as 市,乡镇区划代码 as 市代码  from 全省区划代码 where  LENGTH(乡镇区划代码) < 5");
            adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllCityList");



            //建立第一次左连接
            //即建立本季度全市所有县的本季度数据的数据表
            string sqlToCreateFirstLeftJoin = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (市 TEXT,市代码 TEXT,本季度{Indicator.Value} REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirstLeftJoin);
            string sqlToInsert = $"INSERT INTO  No1LeftJoin  (市,市代码,本季度{Indicator.Value})    select t1.市 as 市,t1.市代码 as 市代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllCityList as t1 left join " +
                $"(select 市代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 市代码) as t2 on t1.市代码 = t2.市代码)";
            adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
            //string sqlforFirstLeftJoin = $"create table FirstLeftJoin  as ( select t1.县 as 县,t1.县代码 as 县代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllCountryListOfThisCity as t1 left join " +
            //    $"(select 县代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 县代码) as t2 on t1.县代码 = t2.县代码))";
            //dt1 = adoForShowSingleMsg.GetDataTable(sqlforFirstLeftJoin);
            //adoForShowSingleMsg.SetDatatableToANewTable(dt1, "No1LeftJoin");
            //第二次左连接
            //
            string sqlToCreateSecondLeftJoin = $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (市 TEXT,市代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecondLeftJoin);
            sqlToInsert = $"INSERT INTO No2LeftJoin (市,市代码,本季度{Indicator.Value},上季度{Indicator.Value}) select t1.市 as 市,t1.市代码 as 市代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                $"(select 市代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 市代码) as t2 on t1.市代码 = t2.市代码)";
            adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
            //string sqlForSecondLeftJoin = $"select t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}] into No2LeftJoin from (No1LeftJoin as t1 left join " +
            //    $"(select 县代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
            //adoForShowSingleMsg.ExecuteBySql(sqlForSecondLeftJoin);
            //第三次左连接 
            string sqlToCreateThirdLeftJoin = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (市 TEXT,市代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅（%）] REAL,[同比增幅（%）] REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateThirdLeftJoin);
            string sqlForThirdLeftJoin = $"INSERT INTO No3LeftJoin(市,市代码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅（%）],[同比增幅（%）]) SELECT  t1.市 as 市,t1.市代码 as 市代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅（%）] , " +
                $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅（%）]  " +
                $"   from ((select * from No2LeftJoin) as t1 left join (select 市代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {withYearAndSeasonTableNameYOY} group by 市代码) as t2 on t1.市代码 = t2.市代码) ";
            adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
            //先把环比增幅和同比增幅的数据类型转成double,在sqlite 中不用转了
            //string sqlForConvertToDouble = $"alter table No3LeftJoin alter column [环比增幅(%)] double";
            //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);
            //sqlForConvertToDouble = $"alter table No3LeftJoin alter column [同比增幅(%)] double";
            //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);

            DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
            double SumOfIndicatorNow = (double)rowsum[0];
            double SumOfIndicatorPre = (double)rowsum[1];
            double SumOfIndicatorYOY = (double)rowsum[2];
            //计算占比
            List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
            if (SumOfIndicatorNow > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
            }
            if (SumOfIndicatorPre > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
            }
            if (SumOfIndicatorYOY > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
            }

            foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
            {
                adoForShowSingleMsg.ExecuteBySql(item);
            }

            DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
            DataRow SumRow = dt.NewRow();
            SumRow[0] = SumRow[1] = "合计";

            for (int i = 2; i < dt.Columns.Count; i++)
            {
                SumRow[i] = (from row in dt.AsEnumerable()
                             select row.Field<double?>(i)).Sum().Value;
                //把饼图需要的数据(就一个value)返回去

            }
            SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
            SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
            dt.Rows.Add(SumRow);


            return dt;
        }

        public override DataTable GetDTSingleIndicatorOfSubordinateThisSeasonByTown(KeyValuePair<string,string> selectedCountry, KeyValuePair<string, string> Indicator)
        {
            MyDBOperator adoForShowSingleMsg = MyDBOperator.DBOperatorInstance;
            //先复制三个表
            adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllTownListOfThisCountry", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });

            DataTable dt1 = adoForShowSingleMsg.GetDataTable($"select 统计区划名称 as 乡,乡镇区划代码 as 乡镇代码  from 全省区划代码 where 乡镇区划代码 like '{selectedCountry.Key}%' and LENGTH(乡镇区划代码) > 6");
            adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllTownListOfThisCountry");



            //建立第一次左连接
            //即建立本季度全市所有县的本季度数据的数据表
            string sqlToCreateFirstLeftJoin = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (乡 TEXT,乡镇代码 TEXT,本季度{Indicator.Value} REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirstLeftJoin);
            string sqlToInsert = $"INSERT INTO  No1LeftJoin  (乡,乡镇代码,本季度{Indicator.Value})    select t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllTownListOfThisCountry as t1 left join " +
                $"(select 乡镇代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码)";
            adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
            //string sqlforFirstLeftJoin = $"create table FirstLeftJoin  as ( select t1.县 as 县,t1.县代码 as 县代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllCountryListOfThisCity as t1 left join " +
            //    $"(select 县代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 县代码) as t2 on t1.县代码 = t2.县代码))";
            //dt1 = adoForShowSingleMsg.GetDataTable(sqlforFirstLeftJoin);
            //adoForShowSingleMsg.SetDatatableToANewTable(dt1, "No1LeftJoin");
            //第二次左连接
            //
            string sqlToCreateSecondLeftJoin = $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (乡 TEXT,乡镇代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecondLeftJoin);
            sqlToInsert = $"INSERT INTO No2LeftJoin (乡,乡镇代码,本季度{Indicator.Value},上季度{Indicator.Value}) select t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                $"(select 乡镇代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码)";
            adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
            //string sqlForSecondLeftJoin = $"select t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}] into No2LeftJoin from (No1LeftJoin as t1 left join " +
            //    $"(select 县代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
            //adoForShowSingleMsg.ExecuteBySql(sqlForSecondLeftJoin);
            //第三次左连接 
            string sqlToCreateThirdLeftJoin = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (乡 TEXT,乡镇代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅（%）] REAL,[同比增幅（%）] REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateThirdLeftJoin);
            string sqlForThirdLeftJoin = $"INSERT INTO No3LeftJoin(乡,乡镇代码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅（%）],[同比增幅（%）]) SELECT  t1.乡 as 乡,t1.乡镇代码 as 乡镇代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅（%）] , " +
                $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅（%）]  " +
                $"   from ((select * from No2LeftJoin) as t1 left join (select 乡镇代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {withYearAndSeasonTableNameYOY} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码) ";
            adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
            //先把环比增幅和同比增幅的数据类型转成double,在sqlite 中不用转了
            //string sqlForConvertToDouble = $"alter table No3LeftJoin alter column [环比增幅(%)] double";
            //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);
            //sqlForConvertToDouble = $"alter table No3LeftJoin alter column [同比增幅(%)] double";
            //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);

            DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
            double SumOfIndicatorNow = (double)rowsum[0];
            double SumOfIndicatorPre = (double)rowsum[1];
            double SumOfIndicatorYOY = (double)rowsum[2];
            //计算占比
            List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
            if (SumOfIndicatorNow > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
            }
            if (SumOfIndicatorPre > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
            }
            if (SumOfIndicatorYOY > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
            }

            foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
            {
                adoForShowSingleMsg.ExecuteBySql(item);
            }

            DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
            DataRow SumRow = dt.NewRow();
            SumRow[0] = SumRow[1] = "合计";

            for (int i = 2; i < dt.Columns.Count; i++)
            {
                SumRow[i] = (from row in dt.AsEnumerable()
                             select row.Field<double?>(i)).Sum().Value;
                //把饼图需要的数据(就一个value)返回去

            }
            SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
            SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
            dt.Rows.Add(SumRow);


            return dt;
        }
        public override DataTable GetDTSingleIndicatorOfSubordinateThisSeasonOfASelectedTown(KeyValuePair<string, string> selectedTown, KeyValuePair<string, string> Indicator)
        {
            MyDBOperator adoForShowSingleMsg = MyDBOperator.DBOperatorInstance;
            //先复制三个表
            adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllFarmerListOfThisTown", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });
            adoForShowSingleMsg.MergeTablesFromDatabaseToAMergeTableInDB(new string[] { withYearAndSeasonTableNameNow, withYearAndSeasonTableNamePre, withYearAndSeasonTableNameYOY},"三个季度表简单合并的表");//这里好像没用
            //DataTable dt1 = adoForShowSingleMsg.GetDataTable($"select 户主姓名,养殖户单位类型,户编码  from 三个季度表简单合并的表 where 乡镇代码 ='{selectdTown.Key}'");
            //adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllFarmerListOfThisTown");

            ////建立第一次左连接
            ////即建立本季度全市所有县的本季度数据的数据表
            //string sqlToCreateFirstLeftJoin = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (户主姓名 TEXT,养殖户单位类型 TEXT,户编码 TEXT,本季度{Indicator.Value} REAL);";
            //adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirstLeftJoin);
            //string sqlToInsert = $"INSERT INTO  No1LeftJoin  (户主姓名,养殖户单位类型,户编码,本季度{Indicator.Value})    select t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型,t1.户编码 as 户编码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllFarmerListOfThisTown as t1 left join " +
            //    $"(select 户主姓名,养殖户单位类型,户编码,cast( [{Indicator.Value}] as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} ) as t2 on t1.户编码 = t2.户编码)";
            //adoForShowSingleMsg.ExecuteBySql(sqlToInsert);

            ////第二次左连接
            ////
            //string sqlToCreateSecondLeftJoin = $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (户主姓名 TEXT,养殖户单位类型 TEXT,户编码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
            //adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecondLeftJoin);
            //sqlToInsert = $"INSERT INTO No2LeftJoin (户主姓名,养殖户单位类型,户编码,本季度{Indicator.Value},上季度{Indicator.Value}) select t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型,t1.户编码 as 户编码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
            //    $"(select 户主姓名,养殖户单位类型,户编码, [{Indicator.Value}] as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} ) as t2 on t1.户编码 = t2.户编码)";
            //adoForShowSingleMsg.ExecuteBySql(sqlToInsert);

            ////第三次左连接 
            //string sqlToCreateThirdLeftJoin = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (户主姓名 TEXT,养殖户单位类型 TEXT,户编码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅（%）] REAL,[同比增幅（%）] REAL);";
            //adoForShowSingleMsg.ExecuteBySql(sqlToCreateThirdLeftJoin);
            //string sqlForThirdLeftJoin = $"INSERT INTO No3LeftJoin(户主姓名,养殖户单位类型,户编码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅（%）],[同比增幅（%）]) SELECT  t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型,t1.户编码 as 户编码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
            //    $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
            //    $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
            //    $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅（%）] , " +
            //    $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅（%）]  " +
            //    $"   from ((select * from No2LeftJoin) as t1 left join (select 户主姓名,养殖户单位类型,户编码,[{Indicator.Value}] as [同比季度{Indicator.Value}] from {withYearAndSeasonTableNameYOY} ) as t2 on t1.户编码 = t2.户编码) ";
            //adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);


            DataTable dt1 = adoForShowSingleMsg.GetDataTable($"select DISTINCT 户主姓名,养殖户单位类型  from 三个季度表简单合并的表 where 乡镇代码 ='{selectedTown.Key}'");
            adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllFarmerListOfThisTown");



            //建立第一次左连接
            //即建立本季度全市所有县的本季度数据的数据表
            string sqlToCreateFirstLeftJoin = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (户主姓名 TEXT,养殖户单位类型 TEXT,本季度{Indicator.Value} REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirstLeftJoin);
            string sqlToInsert = $"INSERT INTO  No1LeftJoin  (户主姓名,养殖户单位类型,本季度{Indicator.Value})    select t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllFarmerListOfThisTown as t1 left join " +
                $"(select 户主姓名,养殖户单位类型,cast( [{Indicator.Value}] as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} ) as t2 on t1.户主姓名= t2.户主姓名)";
            adoForShowSingleMsg.ExecuteBySql(sqlToInsert);

            //第二次左连接
            //
            string sqlToCreateSecondLeftJoin = $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (户主姓名 TEXT,养殖户单位类型 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecondLeftJoin);
            sqlToInsert = $"INSERT INTO No2LeftJoin (户主姓名,养殖户单位类型,本季度{Indicator.Value},上季度{Indicator.Value}) select t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                $"(select 户主姓名,养殖户单位类型, [{Indicator.Value}] as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} ) as t2 on t1.户主姓名= t2.户主姓名)";
            adoForShowSingleMsg.ExecuteBySql(sqlToInsert);

            //第三次左连接 
            string sqlToCreateThirdLeftJoin = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (户主姓名 TEXT,养殖户单位类型  TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅（%）] REAL,[同比增幅（%）] REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateThirdLeftJoin);
            string sqlForThirdLeftJoin = $"INSERT INTO No3LeftJoin(户主姓名,养殖户单位类型,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅（%）],[同比增幅（%）]) SELECT  t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅（%）] , " +
                $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅（%）]  " +
                $"   from ((select * from No2LeftJoin) as t1 left join (select 户主姓名,养殖户单位类型,[{Indicator.Value}] as [同比季度{Indicator.Value}] from {withYearAndSeasonTableNameYOY} ) as t2 on t1.户主姓名= t2.户主姓名) ";
            adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
            
            DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
            double SumOfIndicatorNow = Convert.ToDouble(rowsum.IsNull(0) ? 0 : rowsum[0]);
            double SumOfIndicatorPre = Convert.ToDouble(rowsum.IsNull(1) ? 0 : rowsum[1]);
            double SumOfIndicatorYOY = Convert.ToDouble(rowsum.IsNull(2) ? 0 : rowsum[2]);
            //计算占比
            List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
            if (SumOfIndicatorNow > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
            }
            if (SumOfIndicatorPre > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
            }
            if (SumOfIndicatorYOY > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
            }

            foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
            {
                adoForShowSingleMsg.ExecuteBySql(item);
            }

            DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
            DataRow SumRow = dt.NewRow();
            SumRow[0] = SumRow[1] = "合计";

            for (int i = 2; i < dt.Columns.Count; i++)
            {
                SumRow[i] = (from row in dt.AsEnumerable()
                             select row.Field<double?>(i)).Sum().Value;
                //把饼图需要的数据(就一个value)返回去

            }
            SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
            SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
            dt.Rows.Add(SumRow);


            return dt;
        }
        public override DataTable GetDTSingleIndicatorOfSubordinateThisSeasonByCountry(
            KeyValuePair<string, string> selectedCity, KeyValuePair<string,string> Indicator )
        {
            MyDBOperator adoForShowSingleMsg = MyDBOperator.DBOperatorInstance;
            //先复制三个表
            adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllCountryListOfThisCity", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });
           
            DataTable dt1 = adoForShowSingleMsg.GetDataTable($"select 统计区划名称 as 县,乡镇区划代码 as 县代码  from 全省区划代码 where 乡镇区划代码 like '{selectedCity.Key}%' and LENGTH(乡镇区划代码) < 9");
            adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllCountryListOfThisCity");



            //建立第一次左连接
            //即建立本季度全市所有县的本季度数据的数据表
            string sqlToCreateFirstLeftJoin = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (县 TEXT,县代码 TEXT,本季度{Indicator.Value} REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirstLeftJoin);
            string  sqlToInsert = $"INSERT INTO  No1LeftJoin  (县,县代码,本季度{Indicator.Value})    select t1.县 as 县,t1.县代码 as 县代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllCountryListOfThisCity as t1 left join " +
                $"(select 县代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
            adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
            //string sqlforFirstLeftJoin = $"create table FirstLeftJoin  as ( select t1.县 as 县,t1.县代码 as 县代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllCountryListOfThisCity as t1 left join " +
            //    $"(select 县代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 县代码) as t2 on t1.县代码 = t2.县代码))";
            //dt1 = adoForShowSingleMsg.GetDataTable(sqlforFirstLeftJoin);
            //adoForShowSingleMsg.SetDatatableToANewTable(dt1, "No1LeftJoin");
            //第二次左连接
            //
            string sqlToCreateSecondLeftJoin = $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (县 TEXT,县代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecondLeftJoin);
            sqlToInsert = $"INSERT INTO No2LeftJoin (县,县代码,本季度{Indicator.Value},上季度{Indicator.Value}) select t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                $"(select 县代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
            adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
            //string sqlForSecondLeftJoin = $"select t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}] into No2LeftJoin from (No1LeftJoin as t1 left join " +
            //    $"(select 县代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
            //adoForShowSingleMsg.ExecuteBySql(sqlForSecondLeftJoin);
            //第三次左连接 
            string sqlToCreateThirdLeftJoin = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (县 TEXT,县代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅（%）] REAL,[同比增幅（%）] REAL);";
            adoForShowSingleMsg.ExecuteBySql(sqlToCreateThirdLeftJoin);
            string sqlForThirdLeftJoin = $"INSERT INTO No3LeftJoin(县,县代码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅（%）],[同比增幅（%）]) SELECT  t1.县 as 县,t1.县代码 as 县代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅（%）] , " +
                $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅（%）]  " +
                $"   from ((select * from No2LeftJoin) as t1 left join (select 县代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {withYearAndSeasonTableNameYOY} group by 县代码) as t2 on t1.县代码 = t2.县代码) ";
            adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
            //先把环比增幅和同比增幅的数据类型转成double,在sqlite 中不用转了
            //string sqlForConvertToDouble = $"alter table No3LeftJoin alter column [环比增幅(%)] double";
            //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);
            //sqlForConvertToDouble = $"alter table No3LeftJoin alter column [同比增幅(%)] double";
            //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);

            DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
            double SumOfIndicatorNow = (double)rowsum[0];
            double SumOfIndicatorPre = (double)rowsum[1];
            double SumOfIndicatorYOY = (double)rowsum[2];
            //计算占比
            List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
            if (SumOfIndicatorNow > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
            }
            if (SumOfIndicatorPre > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
            }
            if (SumOfIndicatorYOY > 0)
            {
                ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
            }

            foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
            {
                adoForShowSingleMsg.ExecuteBySql(item);
            }

            DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
            DataRow SumRow = dt.NewRow();
            SumRow[0] = SumRow[1] = "合计";

            for (int i = 2; i < dt.Columns.Count; i++)
            {
                SumRow[i] = (from row in dt.AsEnumerable()
                             select row.Field<double?>(i)).Sum().Value;
                //把饼图需要的数据(就一个value)返回去

            }
            SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
            SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
            dt.Rows.Add(SumRow);


            return dt;
        }

        public override DataTable GetDTOfAllFarmersThisYearOfProvince(string[] tableNames, int 期别季)
        {
            MyDBOperator getSimpleAddOperator = MyDBOperator.DBOperatorInstance;
            getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(tableNames);
            string sqlToGetThisYear =
                 $"  WITH 存栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}' " +
                        " GROUP BY 户编码), " +
                     " 出栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型,SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         " FROM 一到本季度用表 " +
                         " GROUP BY 户编码 )" +
                      "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, COALESCE(c.县, o.县) AS 县,COALESCE(c.县代码, o.县代码) AS 县代码," +
                      "COALESCE(c.乡, o.乡) AS 乡,COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码,COALESCE(c.户编码, o.户编码) AS 户编码,COALESCE(c.户主姓名, o.户主姓名) AS 户主姓名,COALESCE(c.养殖户单位类型, o.养殖户单位类型) AS 养殖户单位类型," +
                      "COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.户编码 = o.户编码 " +
                     " ORDER BY  户编码; ";
            DataTable dt = getSimpleAddOperator.GetDataTable(sqlToGetThisYear);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 9; i < dt.Columns.Count; i++)
            {
                var sum = dt.AsEnumerable().Sum(row => row.Field<double>(i));
                sumRow[i] = sum;
            }
            dt.Rows.Add(sumRow);
            return dt;


         
        }

        public override DataTable GetDTOfAllFarmersThisYearOfCity(KeyValuePair<string, string> selectedCity, string[] tableNames, int 期别季)
        {
            MyDBOperator getSimpleAddOperator = MyDBOperator.DBOperatorInstance;
            getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(tableNames);
            string sqlToGetThisYear =
                 $"  WITH 存栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}'  and 市代码 ='{selectedCity.Key}'" +
                        " GROUP BY 户编码), " +
                     " 出栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型,SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         $" FROM 一到本季度用表 where 市代码 = '{selectedCity.Key}'" +
                         " GROUP BY 户编码 )" +
                      "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, COALESCE(c.县, o.县) AS 县,COALESCE(c.县代码, o.县代码) AS 县代码," +
                      "COALESCE(c.乡, o.乡) AS 乡,COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码,COALESCE(c.户编码, o.户编码) AS 户编码,COALESCE(c.户主姓名, o.户主姓名) AS 户主姓名,COALESCE(c.养殖户单位类型, o.养殖户单位类型) AS 养殖户单位类型," +
                      "COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.户编码 = o.户编码 " +
                     " ORDER BY  户编码; ";
            DataTable dt = getSimpleAddOperator.GetDataTable(sqlToGetThisYear);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 9; i < dt.Columns.Count; i++)
            {
                var sum = dt.AsEnumerable().Sum(row => row.Field<double>(i));
                sumRow[i] = sum;
            }
            dt.Rows.Add(sumRow);
            return dt;
        }

        public override DataTable GetDTOfAllFarmersThisYearOfCountry(KeyValuePair<string, string> selectedCountry, string[] tableNames, int 期别季)
        {
            MyDBOperator getSimpleAddOperator = MyDBOperator.DBOperatorInstance;
            getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(tableNames);
            string sqlToGetThisYear =
                 $"  WITH 存栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}'  and 县代码 ='{selectedCountry.Key}'" +
                        " GROUP BY 户编码), " +
                     " 出栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型,SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         $" FROM 一到本季度用表 where 县代码 = '{selectedCountry.Key}'" +
                         " GROUP BY 户编码 )" +
                      "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, COALESCE(c.县, o.县) AS 县,COALESCE(c.县代码, o.县代码) AS 县代码," +
                      "COALESCE(c.乡, o.乡) AS 乡,COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码,COALESCE(c.户编码, o.户编码) AS 户编码,COALESCE(c.户主姓名, o.户主姓名) AS 户主姓名,COALESCE(c.养殖户单位类型, o.养殖户单位类型) AS 养殖户单位类型," +
                      "COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.户编码 = o.户编码 " +
                     " ORDER BY  户编码; ";
            DataTable dt = getSimpleAddOperator.GetDataTable(sqlToGetThisYear);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 9; i < dt.Columns.Count; i++)
            {
                var sum = dt.AsEnumerable().Sum(row => row.Field<double>(i));
                sumRow[i] = sum;
            }
            dt.Rows.Add(sumRow);
            return dt;
        }

        public override DataTable GetDTOfAllFarmersThisYearOfTown(KeyValuePair<string, string> selectedTown, string[] tableNames, int 期别季)
        {
            MyDBOperator getSimpleAddOperator = MyDBOperator.DBOperatorInstance;
            getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(tableNames);
            string sqlToGetThisYear =
                 $"  WITH 存栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}'  and 乡镇代码 ='{selectedTown.Key}'" +
                        " GROUP BY 户编码), " +
                     " 出栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型,SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         $" FROM 一到本季度用表 where 乡镇代码 = '{selectedTown.Key}'" +
                         " GROUP BY 户编码 )" +
                      "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, COALESCE(c.县, o.县) AS 县,COALESCE(c.县代码, o.县代码) AS 县代码," +
                      "COALESCE(c.乡, o.乡) AS 乡,COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码,COALESCE(c.户编码, o.户编码) AS 户编码,COALESCE(c.户主姓名, o.户主姓名) AS 户主姓名,COALESCE(c.养殖户单位类型, o.养殖户单位类型) AS 养殖户单位类型," +
                      "COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.户编码 = o.户编码 " +
                     " ORDER BY  户编码; ";
            DataTable dt = getSimpleAddOperator.GetDataTable(sqlToGetThisYear);
            DataRow sumRow = dt.NewRow();
            sumRow[0] = "规模户合计";
            for (int i = 9; i < dt.Columns.Count; i++)
            {
                var sum = dt.AsEnumerable().Sum(row => row.Field<double>(i));
                sumRow[i] = sum;
            }
            dt.Rows.Add(sumRow);
            return dt;
        }
    }
}

